/*
 * File :
 * Created : 17 janv. 07
 * By : eSpider Team
 * Version : $Revision$
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details ( see the LICENSE file ).
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
 */

package org.fbmc.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;

import org.fbmc.FreeboxMediaCenter;
import org.fbmc.file.ImageFile;
import org.fbmc.file.MediaFile;
import org.fbmc.file.MusicFile;
import org.fbmc.file.VideoFile;

public class EmbeddedSQLServer {
	/** */
	private final String url;

	/** */
	private Connection conn;
	
	private HashMap<String, PreparedStatement> preparedStatements;

	/**
	 * 
	 * @throws EmbeddedServerException
	 */
	public EmbeddedSQLServer() throws EmbeddedServerException{
		try{
			url = FreeboxMediaCenter.getInstance().getProperty("sql.driver.url");
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
			conn = DriverManager.getConnection(url+";create=true");
			conn.setAutoCommit(false);
			
			setupDatabase();
			
			preparedStatements = new HashMap<String, PreparedStatement>();
		}catch (Exception e) {
			throw new EmbeddedServerException("SQLServer", e.getLocalizedMessage());
		}
	}
	
	private void setupDatabase() throws SQLException{
		if(!conn.getMetaData().getTables(null, null, "MEDIA_FILE", null).next())
			createMediaFileTable();
		if(!conn.getMetaData().getTables(null, null, "MUSIC_FILE", null).next())
			createMusicFileTable();
		if(!conn.getMetaData().getTables(null, null, "IMAGE_FILE", null).next())
			createImageFileTable();
		
		conn.commit();
	}

	/**
	 * 
	 *
	 */
	public void shutdown(){
		try{
			conn.commit();
			conn.close();
			DriverManager.getConnection(url+";shutdown=true");
		}catch(SQLException sqle){}
	}

	/**
	 * 
	 * @return
	 */
	private void createMediaFileTable() throws SQLException{
		conn.createStatement().execute("CREATE TABLE MEDIA_FILE (" +
				"id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT FILE_PK PRIMARY KEY," +
				"path VARCHAR(300) NOT NULL," +
				"name VARCHAR(100) NOT NULL," +
				"size BIGINT NOT NULL," +
				"lastModified BIGINT NOT NULL," +
				"type CHAR(5) NOT NULL)");

		conn.createStatement().execute("CREATE INDEX name_idx ON MEDIA_FILE(name)");
		conn.createStatement().execute("CREATE UNIQUE INDEX path_idx ON MEDIA_FILE(path)");
	}

	/**
	 * 
	 * @return
	 */
	private void createMusicFileTable() throws SQLException{
		conn.createStatement().execute("CREATE TABLE MUSIC_FILE (" +
				"id BIGINT NOT NULL CONSTRAINT music_file_pk PRIMARY KEY," +
				"title VARCHAR(255) NOT NULL," +
				"artist VARCHAR(255) NOT NULL," +
				"album VARCHAR(255) NOT NULL," +
				"CONSTRAINT music_file_fk FOREIGN KEY(id) REFERENCES MEDIA_FILE(id) ON DELETE CASCADE)"
		);
		conn.createStatement().execute("CREATE INDEX title_idx ON MUSIC_FILE(title)");
		conn.createStatement().execute("CREATE INDEX artist_idx ON MUSIC_FILE(artist)");
		conn.createStatement().execute("CREATE INDEX album_idx ON MUSIC_FILE(album)");
	}

	/**
	 * 
	 * @return
	 */
	private void createImageFileTable() throws SQLException{
		conn.createStatement().execute("CREATE TABLE IMAGE_FILE (" +
				"id BIGINT NOT NULL CONSTRAINT image_file_pk PRIMARY KEY," +
				"event VARCHAR(255) NOT NULL," +
				"dateEvent DATE NOT NULL," +
				"CONSTRAINT image_file_fk FOREIGN KEY(id) REFERENCES MEDIA_FILE(id) ON DELETE CASCADE" +
				")"
		);

		conn.createStatement().execute("CREATE INDEX event_idx ON IMAGE_FILE(event)");
		conn.createStatement().execute("CREATE INDEX date_event_idx ON IMAGE_FILE(dateEvent)");
	}	
	
	public synchronized void insertMediaFile(MediaFile mediaFile) throws SQLException{
		if(preparedStatements.get("insertMediaFile") == null){
			preparedStatements.put("insertMediaFile", conn.prepareStatement("INSERT INTO MEDIA_FILE (path, name, size, lastModified, type) VALUES(?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS ));
		}
		
		PreparedStatement ps = preparedStatements.get("insertMediaFile");
		ps.setString(1, mediaFile.getPath());
		ps.setString(2, mediaFile.getName());
		ps.setLong(3, mediaFile.getSize());
		ps.setLong(4, mediaFile.getLastModified());
		ps.setString(5, mediaFile.getType().toString());
		ps.execute();
		
		ResultSet rs = ps.getGeneratedKeys();
		if(rs.next()){
			mediaFile.setId(rs.getLong(1));
					
			if (mediaFile instanceof MusicFile)
				insertMusicFile((MusicFile) mediaFile);
			else if (mediaFile instanceof ImageFile)
				insertImageFile((ImageFile) mediaFile);
		}
	}
	
	
	private void insertMusicFile(MusicFile musicFile) throws SQLException{
		if(preparedStatements.get("insertMusicFile") == null){
			preparedStatements.put("insertMusicFile", conn.prepareStatement("INSERT INTO MUSIC_FILE (id, title, artist, album) VALUES(?, ?, ?, ?)"));
		}
		
		PreparedStatement ps = preparedStatements.get("insertMusicFile");
		ps.setLong(1, musicFile.getId());
		ps.setString(2, musicFile.getTitle());
		ps.setString(3, musicFile.getArtist());
		ps.setString(4, musicFile.getAlbum());
		ps.execute();
	}
	

	private void insertImageFile(ImageFile imageFile) throws SQLException{
		if(preparedStatements.get("insertImageFile") == null){
			preparedStatements.put("insertImageFile", conn.prepareStatement("INSERT INTO IMAGE_FILE (id, event, dateEvent) VALUES(?, ?, ?)"));
		}
		
		PreparedStatement ps = preparedStatements.get("insertImageFile");
		ps.setLong(1, imageFile.getId());
		ps.setString(2, imageFile.getEvent());
		ps.setDate(3, imageFile.getDateEvent());
		ps.execute();
	}
	
	public synchronized void deleteMediaFile(MediaFile mediaFile) throws SQLException{
		if(preparedStatements.get("deleteMediaFile") == null){
			preparedStatements.put("deleteMedia", conn.prepareStatement("DELETE FROM MEDIA_FILE WHERE id=?"));
		}
		
		PreparedStatement ps = preparedStatements.get("deleteMedia");
		ps.setLong(1, mediaFile.getId());
		ps.execute();		
	}
	
	public synchronized void commit() throws SQLException{
		conn.commit();
	}
	
	
	
	public synchronized HashMap<String, MediaFile> retrieveAllMediaFiles() throws SQLException{
		ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM MEDIA_FILE mf LEFT JOIN IMAGE_FILE if ON mf.id=if.id LEFT JOIN MUSIC_FILE muf ON mf.id=muf.id ORDER BY mf.path ASC");
		
		HashMap<String, MediaFile> mediaFiles = new HashMap<String, MediaFile>();
		
		MediaFile mediaFile = null;
		
		while(rs.next()){
			String type = rs.getString("type").toLowerCase();
			
			if(type.equals("music")){
				mediaFile = new MusicFile();
				((MusicFile)mediaFile).setAlbum(rs.getString("album"));
				((MusicFile)mediaFile).setArtist(rs.getString("artist"));
				((MusicFile)mediaFile).setTitle(rs.getString("title"));
			}
			
			else if(type.equals("image")){
				mediaFile = new ImageFile();
				((ImageFile)mediaFile).setEvent(rs.getString("event"));
				((ImageFile)mediaFile).setDateEvent(rs.getDate("dateEvent"));
			}
			
			else if(type.equals("video")){
				mediaFile = new VideoFile();
			}
			
			mediaFile.setId(rs.getLong("id"));
			mediaFile.setLastModified(rs.getLong("lastModified"));
			mediaFile.setName(rs.getString("name"));
			mediaFile.setPath(rs.getString("path"));
			mediaFile.setSize(rs.getLong("size"));

			mediaFiles.put(rs.getString("path"), mediaFile);
		}
		
		return mediaFiles;
	}
}
